﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DataAccessLibrary;
using BusinessLayer.DTO;

namespace BusinessLayer
{

//SELECT * from  PaySlips,Employee
//where Employee.EmployeeID=PaySlips.EmployeeID
//and PaySlips.EmployeeID = 53
//and  Format([GenerationDate],'yyyy-mm-dd') between fromDate and ToDate

    public class ClsSalaryGeneration
    {
        public ClsEmployee employeeInfo;
        public decimal TotalDaysForThisMonth = 0;
        public int TotalDays = 0;
        public int TotalNoOfWorkingdays = 0;
        public int TotalDaysWorked = 0;
        public int TotalDaysOff = 0;
        public int DaysWithOverTime = 0;
        public int NoOfDaysWorkedInHoliday = 0;
        public decimal OverTimeWorked = 0;
        public DateTime fromdate = DateTime.Now;
        public DateTime toDate = DateTime.Now;
        public List<SalaryGenarationInfo> SalaryGenarationInfo = new List<SalaryGenarationInfo>();
        List<DateTime> Leaves = new List<DateTime>();
        private bool IsHolidayOrSunday(DateTime date)
        {
            if (date.DayOfWeek == DayOfWeek.Sunday)
            {
                return true;
            }
            ClsHolidays objClsHolidays = new ClsHolidays();
            if (objClsHolidays.IsHoliday(date))
            {
                return true;
            }
            return false;
        }
        public DataTable GetAllEmployeeForSalaryNOTGeneration(int salaryProcesID)
        {
            string query = "select * from employee where EmployeeID not  in (select EmployeeID from PaySlips where SalaryProcessID= " + salaryProcesID.ToString() + " )";
            using (OleDataClient data = new OleDataClient())
            {
                return data.GetDataTable(query, "Employee");
            }
        }
        public DataTable GetAllEmployeeForSalaryALREADYGenerated(int salaryProcesID)
        {
            string query = "select * from employee where EmployeeID in (select EmployeeID from PaySlips where SalaryProcessID= " + salaryProcesID.ToString() + " )";
            using (OleDataClient data = new OleDataClient())
            {
                return data.GetDataTable(query, "Employee");
            }
        }
        public bool IsSalaryGenerated(int salaryProcesID, int employeeID)
        {
            string query = "select count(*) from PaySlips where SalaryProcessID= " + salaryProcesID.ToString() + " and EmployeeID = " + employeeID.ToString();
            using (OleDataClient data = new OleDataClient())
            {
                string count = data.GetValue(query);
                if (Convert.ToUInt32(count) == 0)
                    return false;
                else
                    return true;
            }
        }
        public void GetAllDetailsForEmployee(int employeeID, int salaryProcesID, ref bool deductSalary, bool SalaryGenerated)
        {
            SalaryGenarationInfo = new List<SalaryGenarationInfo>();
            employeeInfo = new ClsEmployee();
            employeeInfo.GetEmployee(employeeID);
            CLsSalaryProcess objCLsSalaryProcess = new CLsSalaryProcess();
            objCLsSalaryProcess.GetSalaryProcess(salaryProcesID);
            fromdate = objCLsSalaryProcess.FromDate.Value;
            toDate = objCLsSalaryProcess.ToDate.Value;
            if (SalaryGenerated)
            {
                SalaryGenarationInfo = GetGeneratedSalaryDetails(salaryProcesID, employeeID);
                SalaryGenarationInfo.Add(GetGeneratedSalaryHeader(salaryProcesID, employeeID, deductSalary));
            }
            else
            {

                List<AttendanceInfo> lstAttendanceInfo = new List<AttendanceInfo>();
                ClsManageAttendance objClsManageAttendance = new ClsManageAttendance();
                DataTable dt = objClsManageAttendance.GetEmployeeAttendance(employeeID, fromdate, toDate);
                ClsHolidays objClsHolidays = new ClsHolidays();
                ClsAttendance objClsAttendance = new ClsAttendance();
                ClsWorkingHours objClsWorkingHours = new ClsWorkingHours();
                bool found = objClsWorkingHours.GetCurrentWorkingHour(fromdate, toDate);
                if (!found)
                    throw new Exception("Working hours not set for the period.");
                decimal MinimumHourForOverTime = objClsWorkingHours.MinimumHourForOverTime;
                decimal MinimumWorkingHour = objClsWorkingHours.MinimumWorkingHour;
                decimal BasicWorkingHour = objClsWorkingHours.NoOfHours;

                for (DateTime frmDate = fromdate; frmDate <= toDate; frmDate = frmDate.AddDays(1))
                {
                    TotalDays = TotalDays + 1;
                    if (!IsHolidayOrSunday(frmDate))
                    {
                        TotalNoOfWorkingdays = TotalNoOfWorkingdays + 1;
                        DataRow row = dt.AsEnumerable().SingleOrDefault(f => f.Field<DateTime>("DateAttended") == frmDate);
                        if (row == null)
                        {
                            TotalDaysOff = TotalDaysOff + 1;
                            Leaves.Add(frmDate);
                        }
                        else
                        {
                            DateTime? tempEndTime = null;
                            DateTime? tempStartTime = null;
                            tempStartTime = row.Field<DateTime?>("StartTime");
                            if (row.Field<DateTime?>("EndTime") == null)
                            {
                                tempStartTime = row.Field<DateTime?>("StartTime");
                                tempEndTime = tempStartTime.Value.AddHours(Convert.ToDouble(MinimumWorkingHour));
                                objClsAttendance.UpdateEmployeeAttendanceEndTime(employeeID, frmDate, tempEndTime.Value);
                            }
                            else
                            {
                                tempEndTime = row.Field<DateTime?>("EndTime");
                            }

                            DateTime startTime = tempStartTime.Value;
                            DateTime endTime = tempEndTime.Value;
                            decimal noOfHoursWorked = Utility.GetNoOfHours(startTime, endTime);
                            if (noOfHoursWorked < MinimumWorkingHour)
                            {
                                TotalDaysOff = TotalDaysOff + 1;
                                Leaves.Add(frmDate);
                                continue;
                            }
                            TotalDaysWorked = TotalDaysWorked + 1;
                            decimal overTime = noOfHoursWorked - BasicWorkingHour;
                            if (overTime >= MinimumHourForOverTime)
                            {
                                overTime = Math.Round(overTime);
                                DaysWithOverTime = DaysWithOverTime + 1;
                            }
                            else
                            {
                                overTime = 0;
                            }
                            OverTimeWorked = OverTimeWorked + overTime;
                        }
                    }
                    else // Is Holiday
                    {
                        DataRow row = dt.AsEnumerable().SingleOrDefault(f => f.Field<DateTime>("DateAttended") == frmDate);
                        if (row != null)
                        {
                            DateTime? tempEndTime = null;
                            DateTime? tempStartTime = null;
                            tempStartTime = row.Field<DateTime?>("StartTime");
                            if (row.Field<DateTime?>("EndTime") == null)
                            {
                                tempStartTime = row.Field<DateTime?>("StartTime");
                                tempEndTime = tempStartTime.Value.AddHours(Convert.ToDouble(MinimumWorkingHour));
                                objClsAttendance.UpdateEmployeeAttendanceEndTime(employeeID, frmDate, tempEndTime.Value);
                            }
                            else
                            {
                                tempEndTime = row.Field<DateTime?>("EndTime");
                            }

                            DateTime startTime = tempStartTime.Value;
                            DateTime endTime = tempEndTime.Value;
                            decimal noOfHoursWorked = Utility.GetNoOfHours(startTime, endTime);
                            
                            decimal overTime = noOfHoursWorked - BasicWorkingHour;
                            if (overTime >= MinimumHourForOverTime)
                            {
                                overTime = Math.Round(overTime);
                                DaysWithOverTime = DaysWithOverTime + 1;
                            }
                            else
                            {
                                overTime = 0;
                            }
                            OverTimeWorked = OverTimeWorked + overTime;
                        }
                    }
                }
                // End For
                List<SalaryStructureInfo> SalaryStructures = employeeInfo.GetSalaryStructure(employeeInfo.EmployeeID);
                TotalDaysForThisMonth = Convert.ToDecimal((LastDayOfMonthFromDateTime(fromdate) - FirstDayOfMonthFromDateTime(fromdate)).TotalDays + 1);
                SalaryGenarationInfo = new List<SalaryGenarationInfo>();
                foreach (SalaryStructureInfo item in SalaryStructures)
                {
                    SalaryGenarationInfo salaryGenarationInfo = new SalaryGenarationInfo();
                    if (item.Debit)
                    {
                        item.Amount = item.Amount * -1;// item.Amount * -1;
                        salaryGenarationInfo.SalaryEnum = Utility.SalaryEnum.Deductables;
                    }
                    else
                    {
                        salaryGenarationInfo.SalaryEnum = Utility.SalaryEnum.Basics;
                    }
                    decimal actualAmount = 0;
                    if (item.DeductSalaryIfAbsent)
                    {
                        decimal perDaySalary = (item.Amount / TotalDaysForThisMonth);
                        actualAmount = Math.Round(perDaySalary * TotalDays, 2);
                        if (deductSalary)
                        {
                            if (!item.Debit)
                            {
                                decimal leaveDeductAmount = Math.Round(perDaySalary * TotalDaysOff, 2);
                                SalaryGenarationInfo salaryGenarationInfoForLeave = new SalaryGenarationInfo();
                                salaryGenarationInfoForLeave.SalaryHeadID = 0;
                                salaryGenarationInfoForLeave.SalaryHead = "Leave Deduction for " + TotalDaysOff + " Days";
                                salaryGenarationInfoForLeave.Amount = 0;
                                salaryGenarationInfoForLeave.ActualAmount = leaveDeductAmount * -1;
                                salaryGenarationInfoForLeave.Decuctions = leaveDeductAmount; // salaryGenarationInfoForLeave.Amount - salaryGenarationInfoForLeave.ActualAmount;
                                salaryGenarationInfoForLeave.Debit = true ? "Yes" : "No";
                                salaryGenarationInfoForLeave.DeductSalaryIfAbsent = true ? "Yes" : "No";
                                salaryGenarationInfoForLeave.SalaryEnum = Utility.SalaryEnum.Deductables;
                                SalaryGenarationInfo.Add(salaryGenarationInfoForLeave);
                                //actualAmount = Math.Round(actualAmount - (perDaySalary * TotalDaysOff), 2);
                            }
                        }
                    }
                    else
                    {
                        actualAmount = item.Amount;
                    }
                    salaryGenarationInfo.SalaryHeadID = item.SalaryHeadID;
                    salaryGenarationInfo.SalaryHead = item.SalaryHead;
                    salaryGenarationInfo.Amount = actualAmount;// item.Amount;
                    salaryGenarationInfo.ActualAmount = actualAmount;
                    salaryGenarationInfo.Decuctions = salaryGenarationInfo.Amount - salaryGenarationInfo.ActualAmount;
                    salaryGenarationInfo.Debit = item.Debit ? "Yes" : "No";
                    salaryGenarationInfo.DeductSalaryIfAbsent = item.DeductSalaryIfAbsent ? "Yes" : "No";
                    SalaryGenarationInfo.Add(salaryGenarationInfo);
                }
                // Over Time Calculation
                decimal overTimeAmount = OverTimeWorked * employeeInfo.OverTimeRate;
                SalaryGenarationInfo salaryGenarationInfoForOverTime = new SalaryGenarationInfo();
                salaryGenarationInfoForOverTime.SalaryHeadID = 0;
                salaryGenarationInfoForOverTime.SalaryHead = "Over Time for " + OverTimeWorked + " Hours";
                salaryGenarationInfoForOverTime.Amount = overTimeAmount;
                salaryGenarationInfoForOverTime.ActualAmount = overTimeAmount;
                salaryGenarationInfoForOverTime.Decuctions = 0;// salaryGenarationInfoForOverTime.Amount - salaryGenarationInfoForOverTime.ActualAmount;
                salaryGenarationInfoForOverTime.Debit = false ? "Yes" : "No";
                salaryGenarationInfoForOverTime.DeductSalaryIfAbsent = false ? "Yes" : "No";
                salaryGenarationInfoForOverTime.SalaryEnum = Utility.SalaryEnum.OtherIncomes;
                SalaryGenarationInfo.Add(salaryGenarationInfoForOverTime);

                // Total Calculation
                SalaryGenarationInfo salaryGenarationInfoForTotal = new SalaryGenarationInfo();
                salaryGenarationInfoForTotal.SalaryHeadID = 0;
                salaryGenarationInfoForTotal.SalaryHead = "Total Salary ";
                salaryGenarationInfoForTotal.Amount = SalaryGenarationInfo.Sum(x => x.Amount);
                salaryGenarationInfoForTotal.ActualAmount = SalaryGenarationInfo.Sum(x => x.ActualAmount);
                salaryGenarationInfoForTotal.Decuctions = salaryGenarationInfoForTotal.Amount - salaryGenarationInfoForTotal.ActualAmount;
                salaryGenarationInfoForTotal.Debit = "";
                salaryGenarationInfoForTotal.DeductSalaryIfAbsent = "";
                salaryGenarationInfoForTotal.SalaryEnum = Utility.SalaryEnum.TotalSalary;
                SalaryGenarationInfo.Add(salaryGenarationInfoForTotal);
                SalaryGenarationInfo = SalaryGenarationInfo.OrderBy(x => x.SalaryEnum).ToList();
            }
        }

        public void SaveSalary(int employeeID, int salaryProcesID, bool deductSalary)
        {
            SalaryGenarationInfo totalSalary = SalaryGenarationInfo.SingleOrDefault(x => x.SalaryEnum == Utility.SalaryEnum.TotalSalary);
            if (totalSalary != null)
            {
                using (OleDataClient data = new OleDataClient())
                {
                    data.StartTran();
                    try
                    {
                        // Salary Head
                        string strQuery = "insert into PaySlips(SalaryProcessID,EmployeeID,DaysPresent,DaysAbsent,"
                        + " TotalDaysInThisMonth,TotalWorkingDay,TotalDays,TotalOffDays,"
                        + "Amount,Deductions,ActualAmount,TotalOverTimeDay,ToTalOverTimeHour,DeductSalaryForLeave)"
                        + " Values (" + salaryProcesID.ToString() + "," + employeeID.ToString() + "," + TotalDaysWorked.ToString() + "," + TotalDaysOff.ToString() + ","
                         + TotalDaysForThisMonth.ToString() + "," + TotalNoOfWorkingdays.ToString() + "," + TotalDays.ToString() + "," + (TotalDays - TotalNoOfWorkingdays).ToString() + ","
                        + totalSalary.Amount + "," + totalSalary.Decuctions.ToString() + "," + totalSalary.ActualAmount.ToString() + "," + DaysWithOverTime.ToString() + "," + OverTimeWorked.ToString() + "," + deductSalary + ")";
                        data.ExecuteNonQuery(strQuery);
                        int PayID = Convert.ToInt32(data.GetValue("SELECT @@IDENTITY"));

                        // Salary Details
                        foreach (SalaryGenarationInfo item in SalaryGenarationInfo)
                        {
                            if (item.SalaryEnum != Utility.SalaryEnum.TotalSalary)
                            {
                                strQuery = "insert into PaySlipDetails(PayID,SalaryHead,Amount,Type,ActualAmount,Deductions,Deductable,Debit)"
                                + " Values (" + PayID.ToString() + ",'" + item.SalaryHead + "'," + item.Amount.ToString() + ",'" + item.SalaryEnum.ToString()
                                + "'," + item.ActualAmount.ToString() + "," + item.Decuctions.ToString() + "," + item.DeductSalaryIfAbsent + ","
                                + item.Debit + " )";
                                data.ExecuteNonQuery(strQuery);
                            }
                        }

                        // Leave Transaction
                        foreach (DateTime item in Leaves)
                        {
                            strQuery = "insert into LeaveTransaction(EmployeeID,SalaryProcessID,LeaveDate)"
                                        + " Values (" + employeeID.ToString() + "," + salaryProcesID.ToString() + ",'" + item + "')";
                            data.ExecuteNonQuery(strQuery);
                        }

                        // Update Leave Balance
                        if (!deductSalary)
                        {
                            strQuery = "Update AnnualLeave set Balance = Balance - " + Leaves.Count.ToString() + " Where EmployeeID=" + employeeID.ToString();
                            data.ExecuteNonQuery(strQuery);
                        }

                        data.CommitTran();
                    }
                    catch (Exception ex)
                    {
                        data.RollBackTran();
                        throw ex;
                    }
                }
            }
        }
        public void UndoGenerateAllEmployee(int salaryProcesID)
        {
            List<int> empList = new List<int>();
            using (OleDataClient data = new OleDataClient())
            {
                DataTable dt = data.GetDataTable("Select EmployeeID from PaySlips where SalaryProcessID=" + salaryProcesID.ToString(), "");
                foreach (DataRow item in dt.Rows)
                {
                    empList.Add(Convert.ToInt32(item["EmployeeID"]));
                }
            }
            UndoGenerateAll(empList, salaryProcesID);
        }
        public void UndoGenerate(int employeeID, int salaryProcesID)
        {
            List<int> empList = new List<int>();
            empList.Add(employeeID);
            UndoGenerateAll(empList, salaryProcesID);
        }
        private void UndoGenerateAll(List<int> employeeIDList, int salaryProcesID)
        {
            using (OleDataClient data = new OleDataClient())
            {
                data.StartTran();
                try
                {
                    foreach (int item in employeeIDList)
                    {
                        string sql = "select DeductSalaryForLeave from PaySlips where SalaryProcessID=" + salaryProcesID.ToString() + " and  EmployeeID=" + item.ToString();
                        string strDeductSalaryForLeave = data.GetValue(sql);
                        bool DeductSalaryForLeave = Convert.ToBoolean(strDeductSalaryForLeave);
                        int totalLeave = 0;
                        sql = "select DaysAbsent from PaySlips where SalaryProcessID=" + salaryProcesID.ToString() + " and  EmployeeID=" + item.ToString();
                        string count = data.GetValue(sql);
                        totalLeave = Convert.ToInt32(count);
                        sql = "Delete from PaySlipDetails where PayID IN (select PayID from PaySlips where SalaryProcessID=" + salaryProcesID.ToString() + " and  EmployeeID=" + item.ToString() + ")";
                        data.ExecuteNonQuery(sql);
                        sql = "Delete from PaySlips where SalaryProcessID=" + salaryProcesID.ToString() + " and  EmployeeID=" + item.ToString();
                        data.ExecuteNonQuery(sql);
                        sql = "Delete from LeaveTransaction where SalaryProcessID=" + salaryProcesID.ToString() + " and  EmployeeID=" + item.ToString();
                        data.ExecuteNonQuery(sql);
                        if (!DeductSalaryForLeave)
                        {
                            sql = "Update AnnualLeave set Balance = Balance + " + totalLeave.ToString() + " Where EmployeeID=" + item.ToString();
                            data.ExecuteNonQuery(sql);
                        }
                    }
                    data.CommitTran();
                }
                catch (Exception ex)
                {
                    data.RollBackTran();
                    throw ex;
                }
            }
        }
        public DateTime FirstDayOfMonthFromDateTime(DateTime dateTime)
        {
            return new DateTime(dateTime.Year, dateTime.Month, 1);
        }
        public DateTime LastDayOfMonthFromDateTime(DateTime dateTime)
        {
            DateTime firstDayOfTheMonth = new DateTime(dateTime.Year, dateTime.Month, 1);
            return firstDayOfTheMonth.AddMonths(1).AddDays(-1);
        }

        private List<SalaryGenarationInfo> GetGeneratedSalaryDetails(int salaryProcessID, int employeeID)
        {
            List<SalaryGenarationInfo> lstTempSalaryGenarationInfo = new List<SalaryGenarationInfo>();
            DataTable dt = new DataTable();
            using (OleDataClient data = new OleDataClient())
            {
                dt = data.GetDataTable("select * from PaySlipDetails Where PayID=(select PayID from PaySlips Where SalaryProcessID=" + salaryProcessID.ToString() + " and EmployeeID=" + employeeID.ToString() + ")", "");
            }
            SalaryGenarationInfo salaryGenarationInfo = new SalaryGenarationInfo();
            foreach (DataRow item in dt.AsEnumerable().AsQueryable())
            {
                salaryGenarationInfo = new SalaryGenarationInfo();
                salaryGenarationInfo.ActualAmount = item.Field<decimal>("ActualAmount");
                salaryGenarationInfo.Amount = item.Field<Decimal>("Amount");
                salaryGenarationInfo.Debit = item.Field<bool>("Debit") ? "Yes" : "No";
                salaryGenarationInfo.Decuctions = item.Field<decimal>("Deductions");
                salaryGenarationInfo.DeductSalaryIfAbsent = item.Field<bool>("Deductable") ? "Yes" : "No";
                salaryGenarationInfo.SalaryEnum = (Utility.SalaryEnum)Enum.Parse(typeof(Utility.SalaryEnum), item.Field<string>("Type"));
                salaryGenarationInfo.SalaryHead = item.Field<string>("SalaryHead");
                salaryGenarationInfo.SalaryHeadID = item.Field<Int32>("PayID");
                lstTempSalaryGenarationInfo.Add(salaryGenarationInfo);
            }
            return lstTempSalaryGenarationInfo;
        }
        private SalaryGenarationInfo GetGeneratedSalaryHeader(int salaryProcessID, int employeeID, bool deductSalary)
        {
            DataTable dt = new DataTable();
            using (OleDataClient data = new OleDataClient())
            {
                dt = data.GetDataTable("select * from PaySlips Where SalaryProcessID=" + salaryProcessID.ToString() + " and EmployeeID=" + employeeID.ToString(), "");
            }
            SalaryGenarationInfo salaryGenarationInfo = new SalaryGenarationInfo();
            foreach (DataRow item in dt.AsEnumerable().AsQueryable())
            {
                salaryGenarationInfo = new SalaryGenarationInfo();
                salaryGenarationInfo.ActualAmount = item.Field<decimal>("ActualAmount");
                salaryGenarationInfo.Amount = item.Field<Decimal>("Amount");
                salaryGenarationInfo.Debit = "";
                salaryGenarationInfo.Decuctions = item.Field<decimal>("Deductions");
                salaryGenarationInfo.DeductSalaryIfAbsent = "";
                salaryGenarationInfo.SalaryEnum = Utility.SalaryEnum.TotalSalary;
                salaryGenarationInfo.SalaryHead = "Total Salary ";
                salaryGenarationInfo.SalaryHeadID = 0;

                TotalDaysForThisMonth = item.Field<int>("TotalDaysInThisMonth");
                TotalDays = item.Field<int>("TotalDays");
                TotalNoOfWorkingdays = item.Field<int>("TotalWorkingDay");
                TotalDaysWorked = item.Field<int>("DaysPresent");
                TotalDaysOff = item.Field<int>("DaysAbsent");
                DaysWithOverTime = item.Field<int>("TotalOverTimeDay");
                OverTimeWorked = item.Field<int>("ToTalOverTimeHour");
                deductSalary = item.Field<bool>("DeductSalaryForLeave");
            }
            return salaryGenarationInfo;
        }
        public DataTable GetSalaryPaidPerEmployee(int employeeID, DateTime fromDate,DateTime toDate)
        {
            using (OleDataClient data = new OleDataClient())
            {
                DataTable dt = data.GetDataTable("Select * from PaySlips where Format([GenerationDate],'yyyy-mm-dd') between '" + Utility.GetDateYYYYMMDD(fromDate) + "' and '" + Utility.GetDateYYYYMMDD(toDate) + "' and EmployeeID=" + employeeID.ToString(), "PaySlips");
                return dt;
            }

        }
    }

}
